package com.scalar.chiptrack.adaptor.tsmc;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.adaptor.wipfed.WipFedStatus;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.FabSortLot;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.HashMap;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

/**
 * Created by IntelliJ IDEA.
 * User: Sudhakar
 * Date: Jun 14, 2007
 * Time: 7:40:41 PM
 */
public class TSMCDAO implements AdaptorConstants
{

    private static Logger s_logger = null;


    /**
     *
     * @param dbTransaction
     * @param tsmcWipList
     * @throws ScalarException
     */
    public static void saveTSMCWipDetails(DBTransaction dbTransaction, ArrayList tsmcWipList) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        TSMCLotStatus tsmcLotStatus = null;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection(2);
            }
            else
            {
                conn = dbTransaction.getConnection(2);
            }

            Calendar cal = Calendar.getInstance();
            java.util.Date todayDate = cal.getTime();
            String currentDate = StringUtils.formatDateToString(todayDate, "MM/dd/yyyy");
            // Step 1
            // Delete all THE PREVIOUS DATA IN TABLE
            //pstmt = conn.prepareStatement("DELETE FROM Tsmc_Wip_Table");
            pstmt = conn.prepareStatement("DELETE FROM tblLotStatus WHERE ImportDate = ?");
            pstmt.setString(1, currentDate);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            /*// Delete all THE PREVIOUS DATA IN HISTORY TABLE
            pstmt = conn.prepareStatement("DELETE FROM Tsmc_Wip_History_Table WHERE ADAPTOR_DATE = ?");
            pstmt.setString(1, currentDate);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;*/

            //pstmt = conn.prepareStatement("{ call sp_save_tsmc_wip_info ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");
            pstmt = conn.prepareStatement("INSERT INTO tblLotStatus( [Customer Name],[Fab Group],[Foundry Part Name],[Customer Part Name],[PO#],[Lot Id],[Lot Type], \n" +
                    " [Lot State],[Stage Name],[Curr Stage #],[Lot Wfr Qty],[Actual Start Date],[Act Fabout Date],[CRD],[ImportDate] ) \t\t\n" +
                    " VALUES( 'Mobius Microsystems', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");

            Iterator lotEventHistoryIterator = tsmcWipList.iterator();
            while (lotEventHistoryIterator.hasNext())
            {
                tsmcLotStatus = (TSMCLotStatus) lotEventHistoryIterator.next();

                //log( tsmcShipList + "\n" );

                pstmt.setString(1, tsmcLotStatus.getWaferFab());
                pstmt.setString(2, tsmcLotStatus.getTsmcProduct());
                pstmt.setString(3, tsmcLotStatus.getCustomerProduct());
                pstmt.setString(4, tsmcLotStatus.getCustomerPo() );
                pstmt.setString(5, tsmcLotStatus.getTsmcLotID());
                pstmt.setString(6, "PROD");
                pstmt.setString(7, tsmcLotStatus.getHoldFlag() != null ? "Hold" : "In Progress");
                pstmt.setString(8, tsmcLotStatus.getCurrentStage());
                pstmt.setString(9, tsmcLotStatus.getStageSeq());
                pstmt.setString(10, tsmcLotStatus.getWaferQty());
                pstmt.setString(11, tsmcLotStatus.getWaferStartDate());
                pstmt.setString(12, tsmcLotStatus.getCommitDate());
                pstmt.setString(13, tsmcLotStatus.getProjectedOutDate() );
                pstmt.setString(14, currentDate);


                /*pstmt.setString(9, tsmcLotStatus.getDieQty());
                pstmt.setString(10, tsmcLotStatus.getHoldFlag());
                pstmt.setString(11, tsmcLotStatus.getWaferID());
                pstmt.setString(12, tsmcLotStatus.getUpdateMode());
                pstmt.setString(13, tsmcLotStatus.getCustomerLotID());*/



                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }

        catch (SQLException sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not save TSMC WIP Information into database", sqle);
        }

        finally
        {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {
                }
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
    }

    public static void saveTSMCShipDetails(DBTransaction dbTransaction, ArrayList shipList) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;


        TSMCShippingInfo shipStatus = null;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection(2);
            }
            else
            {
                conn = dbTransaction.getConnection(2);
            }

            /*Calendar cal = Calendar.getInstance();
            java.util.Date todayDate = cal.getTime();
            String currentDate = StringUtils.formatDateToString(todayDate, "MM/dd/yyyy");*/

            // Delete all THE PREVIOUS DATA IN TABLE
            /*pstmt = conn.prepareStatement("DELETE FROM Chartered_Ship_Status_Table");
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;*/

            // Delete all THE PREVIOUS DATA IN HISTORY TABLE
            /*pstmt = conn.prepareStatement("DELETE FROM Chartered_Ship_Status_History_Table WHERE ADAPTOR_DATE = ?");
            pstmt.setString(1, currentDate);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;*/

            //pstmt = conn.prepareStatement("{ call sp_save_chartered_ship_info ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");
            pstmt = conn.prepareStatement("INSERT INTO tblShipAlert ( [Customer Name],[Ship Date],[Invoice #],[Invoice Date],[PO#],[Customer Partname],[Foundry Part Name],[Lot Id],[Lot Type],[Bill Qty (Wfr)],[Wfr Ids],[v Bill Die]," +
                    "[Ship To Location] )" +
                    " VALUES ('Mobius Microsystems', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ");


            Iterator shipIterator = shipList.iterator();
            while ( shipIterator.hasNext() )
            {
                shipStatus = ( TSMCShippingInfo ) shipIterator.next();

                //log( shipStatus + "\n" );
                if (shipStatus.getTsmcLotID() == null ) continue;
                pstmt.setString(1, shipStatus.getInvoiceDate());
                pstmt.setString(2, shipStatus.getInvoiceNo() );
                pstmt.setString(3, shipStatus.getInvoiceDate() );
                pstmt.setString(4, shipStatus.getCustomerPO() );
                pstmt.setString(5, shipStatus.getCustomerProduct());
                pstmt.setString(6, shipStatus.getTsmcProduct());
                pstmt.setString(7, shipStatus.getTsmcLotID() );
                pstmt.setString(8, "PROD" );
                pstmt.setString(9,  shipStatus.getWaferQty() );
                pstmt.setString(10, shipStatus.getWaferID() );
                pstmt.setString(11, shipStatus.getDieQty() );
                pstmt.setString(12, shipStatus.getShipTo() );

                //pstmt.setString(27, currentDate);

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            success = true;
        }

        catch (SQLException sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not save TSMC Ship Information into database", sqle);
        }

        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        } else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {
                }
            }

            if (pstmt != null) {
                try {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
    }
    /**
     *
     * @param dbTransaction
     * @param wipList
     * @throws ScalarException
     */
    public static void saveWipFedTSMCDetails(DBTransaction dbTransaction, ArrayList wipList) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        WipFedStatus wipStatus = null;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            Calendar cal = Calendar.getInstance();
            java.util.Date todayDate = cal.getTime();
            String currentDate = StringUtils.formatDateToString(todayDate, "MM/dd/yyyy");
            // Step 1
            // Delete all THE PREVIOUS DATA IN TABLE
            pstmt = conn.prepareStatement("DELETE FROM Wip_Fed_TSMC_Table");
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            // Delete all THE PREVIOUS DATA IN HISTORY TABLE
            pstmt = conn.prepareStatement("DELETE FROM Wip_Fed_TSMC_History_Table WHERE FILE_DATE = ?");
            pstmt.setString(1, currentDate);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement("{ call sp_save_wip_Fed_tsmc_status ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }");

            Iterator wipStatusIterator = wipList.iterator();
            while (wipStatusIterator.hasNext())
            {
                wipStatus = (WipFedStatus) wipStatusIterator.next();

                log(wipStatus.toString());

                if (wipStatus.getCharteredPartNo() != null)
                {
                    pstmt.setString(1, wipStatus.getLotNumber());
                    pstmt.setString(2, wipStatus.getCharteredPartNo());
                    pstmt.setString(3, wipStatus.getStartDate());
                    pstmt.setString(4, wipStatus.getRsDate());
                    pstmt.setString(5, wipStatus.getOsDate());
                    pstmt.setString(6, wipStatus.getOutDate());
                    pstmt.setString(7, wipStatus.getMesQty());
                    pstmt.setString(8, wipStatus.getMesStep());
                    pstmt.setString(9, wipStatus.getPsType());
                    pstmt.setString(10, currentDate);
                    pstmt.addBatch();
                }
            }

            pstmt.executeBatch();
            success = true;
        }

        catch (SQLException sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException("Could not save WIP FED TSMC  Wip Information into database", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore) {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore) {
                }
            }
        }
    }

    /**
     *
     * @return
     * @throws Exception
     */
    public static HashMap getTSMCFabList() throws Exception
    {
        HashMap tsmcMap         = new HashMap();
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        String partNumber       = null;
        String lotNumber        = null;
        FabSortLot fabSortLot   = null;

        String actStartDate;
        String outDate;
        String rsdate;
        String osDate;
        String waferQty;
        String stage;
        String psType;
        String waferId;


        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(" SELECT LOT_NUMBER, PART_NUMBER, \n" +
                    "START_DATE, RS_DATE, OS_DATE, OUT_DATE, QTY, MES_STEP,  PS_TYPE, \n" +
                    "NULL as WAFER_ID, NULL AS CURRENT_STAGE FROM WIP_FED_TSMC_TABLE \n" +
                    "UNION\n" +
                    "SELECT tsmc_lot_id, CUSTOMER_PRODUCT, wafer_START_DATE, null, null,null, \n" +
                    "WAFER_Start_QTY, CURRENT_STAGE, 'FAB' as PS_TYPE, WAFER_ID, CURRENT_STAGE  \n" +
                    "FROM  Tsmc_Wip_Table" );

            rset = pstmt.executeQuery();

            while ( rset.next() )
            {


                lotNumber       = rset.getString( "LOT_NUMBER" );
                partNumber      = rset.getString( "PART_NUMBER" );
                actStartDate    = rset.getString( "START_DATE" );
                rsdate          = rset.getString( "RS_DATE" );
                osDate          = rset.getString( "OS_DATE" );                
                outDate         =  rset.getString( "OUT_DATE" );
                waferQty        = rset.getString( "QTY" );
                stage           = rset.getString( "MES_STEP" );
                psType          = rset.getString( "PS_TYPE" );
                waferId         = rset.getString( "WAFER_ID" );
                stage           = rset.getString( "CURRENT_STAGE" );

                if (lotNumber != null && partNumber != null)
                {
                    fabSortLot = (FabSortLot) tsmcMap.get(partNumber + lotNumber);
                    if (fabSortLot == null)
                    {
                        fabSortLot = new FabSortLot();
                        tsmcMap.put(partNumber + lotNumber, fabSortLot);
                    }
                    int quantity = 0;
                    if (fabSortLot.getQtyIn() != null)
                    {
                        try
                        {
                            quantity = Integer.parseInt(fabSortLot.getQtyIn());
                        }
                        catch (NumberFormatException ignore)
                        {
                        }
                        try
                        {
                            quantity += Integer.parseInt( waferQty );
                        }
                        catch (NumberFormatException ignore)
                        {
                        }
                    }
                    else
                    {
                        try
                        {
                            quantity = Integer.parseInt( waferQty );
                        }
                        catch (NumberFormatException ignore)
                        {
                        }
                    }

                   String dateIn            = null;
                   String shipFcstDate      = null;
                   String expectedDateOut   = null;
                   String outLookDate       = null;

                    if ( actStartDate != null && actStartDate.trim().length() > 0)
                    {
                        dateIn = StringUtils.formatDate(actStartDate, "dd-MMM-yy", "MM/dd/yyyy");
                    }

                    if ( osDate != null && osDate.trim().length() > 0)
                    {
                        expectedDateOut = StringUtils.formatDate( osDate, "dd-MMM-yy", "MM/dd/yyyy");
                    }

                    if (rsdate != null && rsdate.trim().length() > 0)
                    {
                        outLookDate = StringUtils.formatDate( rsdate, "dd-MMM-yy", "MM/dd/yyyy");
                    }

                    if ( outDate != null && outDate.trim().length() > 0)
                    {
                        shipFcstDate = StringUtils.formatDate(outDate, "dd-MMM-yy", "MM/dd/yyyy");
                    }

                     if ( stage != null && "HELD".equalsIgnoreCase( stage ) )
                    {
                        fabSortLot.setLotStatus( AdaptorConstants.LOT_STATUS_HOLD );
                    }
                    else
                    {
                        fabSortLot.setLotStatus( AdaptorConstants.LOT_STATUS_RUNNING );
                    }

                    fabSortLot.setLotId(lotNumber);
                    fabSortLot.setCustProduct(partNumber);
                    fabSortLot.setDateIn(dateIn);
                    fabSortLot.setQtyIn(quantity + "");
                    fabSortLot.setStage(stage);
                    fabSortLot.setForcastedDateOut(expectedDateOut);
                    fabSortLot.setOutLookDate(outLookDate);
                    fabSortLot.setVendorName(VENDOR_NAME_TSMC);
                    fabSortLot.setWaferID(waferQty);
                    fabSortLot.setPsType(psType);
                    fabSortLot.setWaferID( waferId );
                    fabSortLot.setShip_fcst_date(shipFcstDate);
                    fabSortLot.setLotCategory(LOT_CATEGORY_TYPE_PROD);
                    fabSortLot.setFabLotNumber(lotNumber);
                }
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all TSMC Lots from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return tsmcMap;
    }

    /**
     *
     * @param message
     */
    private static void log (String message)
    {
        if ( message == null) return;

        if (s_logger != null)
        {
            synchronized (s_logger)
            {
                s_logger.logDebug (message + LINE_SEPARATOR);
            }
        }
        else
        {
            System.out.println (message);
        }
    }
    /**
     *
     * @param logger
     */
    public static void setLogger (Logger logger)
    {
        s_logger = logger;
    }

}
